﻿Public Class clsCPCK

    Public Function getList(status As String, prod_spec_id As String) As DataTable
        Dim szSql As String = String.Empty
        szSql &= "select a.*,round(cast(date_part('epoch',now()-into_time)/60/60 as numeric),0) as hour_length from sf.b_lh_cache_area a "
        szSql &= " left join sf.b_prod_info b on a.prod_id = b.prod_id"
        szSql &= " where 1 = 1 "
        szSql &= " and a.status = '" + status + "'"
        szSql &= " and a.prod_spec_id = '" + prod_spec_id + "'"
        szSql &= " order by hour_length desc "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        Return dt
    End Function


    Public Function getList(status As String) As DataTable
        Dim szSql As String = String.Empty
        szSql &= "select *,round(cast(date_part('epoch',now()-into_time)/60/60 as numeric),0) as hour_length from sf.b_lh_cache_area where 1 = 1 "
        szSql &= " and status = '" + status + "'"
        szSql &= " order by hour_length desc "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)
        Return dt
    End Function

    Public Function getList(status As String, prod_id As String, wh_no As String,
                            into_time_start As String, into_time_end As String,
                            out_time_start As String, out_time_end As String) As DataTable
        Dim szSql As String = ""
        Dim dt As DataTable = Nothing
        szSql &= "select *,round(cast(date_part('epoch',now()-into_time)/60/60 as numeric),0) as hour_length from sf.b_lh_cache_area where 1 = 1 "
        If String.IsNullOrEmpty(status) = False Then
            szSql &= " and status = '" + status + "'"
        End If
        If String.IsNullOrEmpty(prod_id) = False Then
            szSql &= " and prod_id like '%" + prod_id + "%'"
        End If
        If String.IsNullOrEmpty(wh_no) = False Then
            szSql &= " and wh_no = '" + wh_no + "'"
        End If
        If status.Equals("0") Then
            If String.IsNullOrEmpty(into_time_start) = False And String.IsNullOrEmpty(into_time_end) = False Then
                szSql &= " and (into_time >= '" + DateTime.Parse(into_time_start + " 00:00:00.000") + "'"
                szSql &= " and into_time <= '" + DateTime.Parse(into_time_end + " 23:59:59.999") + "')"
            End If
        End If
        If status.Equals("1") Then
            If String.IsNullOrEmpty(out_time_start) = False And String.IsNullOrEmpty(out_time_end) = False Then
                szSql &= " and (out_time >= '" + DateTime.Parse(out_time_start + " 00:00:00.000") + "'"
                szSql &= " and out_time <= '" + DateTime.Parse(out_time_end + " 23:59:59.999") + "')"
            End If
        End If
        dt = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getList = New DataTable
        Else
            getList = dt
        End If
    End Function

    Public Function getListByProdId(prodid As String, status As String) As DataTable
        Dim szSql As String = ""
        Dim dt As DataTable = Nothing
        szSql &= "select *,round(cast(date_part('epoch',now() - into_time)/60/60 as numeric),0) as hour_length from sf.b_lh_cache_area where 1 = 1 "
        szSql &= " and prod_id = '" + prodid + "'"
        szSql &= " and status = '" + status + "'"
        dt = DBacs.ExecuteSql(szSql, -1)
        If IsNothing(dt) Then
            getListByProdId = New DataTable
        Else
            getListByProdId = dt
        End If
    End Function

    Public Function outCK(_loc As String, oper As String, _sys_id As Integer, prod_id As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= " UPDATE sf.b_lh_cache_area "
            sql &= " SET out_time = (SELECT CURRENT_TIMESTAMP(6)) , status = ?, out_ope = ? "
            sql &= " WHERE factory_id = ? and sys_id = ? "

            rtn = DBacs.ExecuteUpdateSql(sql, -1, "1", oper, "TS", _sys_id)

            sql = String.Empty
            sql &= " UPDATE sf.t_mst_storage_location "
            sql &= " SET status = ? "
            sql &= " WHERE location_no = ? "

            rtn = DBacs.ExecuteUpdateSql(sql, -1, "0", _loc)

            sql = String.Empty
            sql &= "update sf.b_prod_info set out_storage_time =(SELECT CURRENT_TIMESTAMP(6)), update_time=(SELECT CURRENT_TIMESTAMP(6)),"
            sql &= "status= ? "
            sql &= " where factory_id = ? and prod_id = ?"
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "4", "TS", prod_id)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

End Class
